package ai;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author mammad
 */
public class Jdbc {

    private QueryForm form;

    public Jdbc(QueryForm pForm) {
        form = pForm;


    }

    /*public void openConnection() {
    try {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    String url = "jdbc:oracle:thin:@tycho.ita.chalmers.se:1521/kingu.ita.chalmers.se";
    String userName = "TDA357_029"; // Your username goes here!
    String password = "B5TR5s"; // Your password goes here!
    Connection conn = DriverManager.getConnection(url, userName, password);
    //String student = args[0]; // This is the identifier for the student.
    } catch (SQLException e) {
    System.err.println(e);
    System.exit(2);
    }
    }*/
    //***********kolla om mål med en ingrediens existerar
   /* public boolean checkAnIncIngrediens(Connection conn) {

        // openConnection();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkInc = "SELECT mealname FROM meal m WHERE ExISTS (SELECT * from mealingrediens mi  where mi.mealname=m.mealname and ingrediens ='" + form.getIncIngrediens().get(0) + "')";
            System.out.println(checkInc);
            ResultSet Result = mealIngrediens.executeQuery(checkInc);
            Result.next();
            String a = Result.getString(1);
            System.out.println(a);
            form.addFinalMeals(a);


            // conn.close();

            return Result.next();

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }

    }*/


    //***********kolla om mål utan en ingrediens existerar
   /* public boolean checkAnExcIngrediens(Connection conn) {

        //openConnection();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "SELECT mealname FROM meal m WHERE not ExISTS (SELECT * from mealingrediens mi  where mi.mealname=m.mealname and ingrediens ='" + form.getExcIngrediens().get(0) + "'" + ")";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            // conn.close();
            return Result.next();

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }

    }*/

    public boolean checkIncMealCategories(Connection conn) {

        // openConnection();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select * from mealcategories where categoryname ='";
            for (int i = 0; i < form.getMealCategory().size(); i++) {
                checkExc += form.getMealCategory().get(i) + "'";
                if (i == form.getIncIngrediens().size() - 1) {
                    checkExc += " and categoryname = '";
                }

            }
         //   System.out.println(checkExc);
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            // conn.close();
            return Result.next();

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }

    }
    public boolean checkExcMealCategories(Connection conn) {
 ArrayList<String> queries = new ArrayList<String>();
        form.getFinalMeals().clear();
        //openConnection();
        boolean exist = false;
        try {


            for (int i = 0; i < form.getMealCategory().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS (SELECT * from categoryname mi  where mi.mealname=m.mealname and CATEGORYNAME  ='" + form.getMealCategory().get(i) + "'" + ")";
                //            System.out.println(checkInc);
                queries.add(checkInc);

            }
            Statement mealIngrediens = conn.createStatement();
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }
            }
                ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;
            }

            }catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
              return exist;
    }

      

    

    public boolean checkmeal(Connection conn) {
        //   openConnection();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select * from meal where mealname ='" + form.getMeal().get(0) + "'";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            //	 conn.close();
            return Result.next();

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }

    }

    public boolean checkOrIncIngrediens(Connection conn) {
        // openConnection();
        boolean exist = false;
        try {
            Statement mealIngrediens = conn.createStatement();

            String checkInc = "SELECT distinct mealname FROM mealingrediens m WHERE ingrediens ='";
            for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                checkInc += form.getIncIngrediens().get(i) + "'";
                if (i != form.getIncIngrediens().size() - 1) {
                    checkInc += " or ingrediens = '";
                }

            }
            ResultSet Result = mealIngrediens.executeQuery(checkInc);



            while (Result.next()) {
                exist = true;
           //    System.out.println(Result.getString(1));
                form.addFinalMeals(Result.getString(1));

            }



        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();

        }
        return exist;

    }

    public boolean checkAndIncIngrediens(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.getFinalMeals().clear();
        //openConnection();
        boolean exist = false;
        try {
            Statement mealIngrediens = conn.createStatement();
            for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                String checkInc = "select mealname from mealingrediens where ingrediens ='" + form.getIncIngrediens().get(i) + "'";
                queries.add(checkInc);

            }
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }

            }


            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;

            //System.out.println(Result.getString(1));

//                form.addFinalMeals(Result.getString(1));
            }
        //conn.close();


        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;

    }

    public boolean checkAndExcIngrediens(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.getFinalMeals().clear();
        //openConnection();
        boolean exist = false;
        try {
            System.out.println(form.getExcIngrediens().size());

            for (int i = 0; i < form.getExcIngrediens().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS (SELECT * from mealingrediens mi  where mi.mealname=m.mealname and ingrediens ='" + form.getExcIngrediens().get(i) + "'" + ")";
                //            System.out.println(checkInc);
                queries.add(checkInc);

            }
            Statement mealIngrediens = conn.createStatement();
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }

            }

          //  System.out.println(query);
            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;

         //   System.out.println(Result.getString(1));

//                form.addFinalMeals(Result.getString(1));
            }
        //conn.close();


        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;

    }
     public boolean checkOrExcIngrediens(Connection conn) {
         /*******************************do this function***********/
         
         
         return true;
     }
     
     
     
      public boolean checkAndIncAndExcIngrediens(Connection conn) {
          String queryInc = "select * from(";
        ArrayList<String> queriesInc = new ArrayList<String>();
        form.getFinalMeals().clear();
        boolean exist = false;
        try {
            Statement mealIngrediens = conn.createStatement();
            for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                String checkInc = "select mealname from mealingrediens where ingrediens ='" + form.getIncIngrediens().get(i) + "'";

                System.out.println(checkInc+ "     inc");
                queriesInc.add(checkInc);

            }

            for (int i = 0; i < queriesInc.size(); i++) {

                queryInc += queriesInc.get(i) + ")";
                if (i != queriesInc.size() - 1) {
                    queryInc += "natural join (";
                }

            }





        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        
        
        ArrayList<String> queriesExc = new ArrayList<String>();
        form.getFinalMeals().clear();
        //openConnection();
        boolean existExc = false;
        try {
            System.out.println(form.getExcIngrediens().size());

            for (int i = 0; i < form.getExcIngrediens().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS (SELECT * from mealingrediens mi  where mi.mealname=m.mealname and ingrediens ='" + form.getExcIngrediens().get(i) + "'" + ")";
                            System.out.println(checkInc +"    EXC");
                queriesExc.add(checkInc);

            }
            Statement mealIngrediens = conn.createStatement();
            String queryExc = "select * from(";
            for (int i = 0; i < queriesExc.size(); i++) {

                queryExc += queriesExc.get(i) + ")";
                if (i != queriesExc.size() - 1) {
                    queryExc += "natural join (";
                }

            }

           // System.out.println(queryExc);
           /* ResultSet ResultExc = mealIngrediens.executeQuery(queryExc);
            while (ResultExc.next()) {
                form.addFinalMeals(ResultExc.getString(1));
                exist = true;

            System.out.println(ResultExc.getString(1));


            }*/
                String queryTot= queryInc+ "natural join (" + queryExc+")";
                System.out.println(queryTot);
                ResultSet ResultTotal = mealIngrediens.executeQuery(queryTot);
                 while (ResultTotal.next()) {
                form.addFinalMeals(ResultTotal.getString(1));
                exist = true;

          


            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;

    }


    
     
     
     

    public void getDatabaseIngr(Connection conn) {


        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select distinct(ingrediens) from mealingrediens";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);

            while (Result.next()) {

            form.addDatabaseIngr(Result.getString(1));

            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();

        }

    }
    public void getDatabaseMealCat(Connection conn) {

        //openConnection();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select distinct categoryname from mealcategories";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);

            while (Result.next()) {

            form.addDatabaseMealcat(Result.getString(1));

            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();

        }

    }

    public void finalMeal(Connection conn) {

        try {
            Statement fmeal = conn.createStatement();

            for (int i = 0; i < form.getFinalMeals().size(); i++) {
                String check1 = "select mi.mealname,TIMETOPREPARE , INGREDIENS,AMOUNTINGREDIENS,UNIT,WAYTOCOOCK,EQUIPMENTDEMANDING from meal m,mealingrediens mi, waytocoock w where m.mealname = mi.mealname and m.mealname = w.mealname and m.mealname ='" + form.getFinalMeals().get(i) + "'";

                String check2 = "select * from meal where mealname = '" + form.getFinalMeals().get(i) + "'";
                String check3 = "select * from WAYTOCOOCK where mealname = '" + form.getFinalMeals().get(i) + "'";


                ResultSet Result2 = fmeal.executeQuery(check2);


                Result2.next();
                System.out.println("Mealname:  " + Result2.getString(1) + "\t" + "Time to prepare   " + Result2.getString(2) + "\n");
                ResultSet Result1 = fmeal.executeQuery(check1);
                System.out.println("Ingrediens\t Amount \t Unit" + "\n");
                while (Result1.next()) {
                    System.out.println(Result1.getString(3) + "\t\t" + Result1.getString(4) + "\t\t" + Result1.getString(5) + "\n");
                }

                ResultSet Result3 = fmeal.executeQuery(check3);
                Result3.next();
                System.out.println("Way to coock; \n");

                System.out.println(Result3.getString(2) + "\n");
            }


        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();

        }

    }
    }
